package com.llh.controller.excel;

import com.llh.bean.Pe;
import com.llh.bean.StockBase;
import com.llh.mapper.PeMapper;
import com.llh.mapper.StockBaseMapper;
import com.llh.util.PoiUtil;
import com.llh.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

public class ReadPeExcel {
    /**
     * 读其他两张表
     * @throws Exception
     */
    public static void ReadPe() {
        //读第一张表
        int sheerNum = 0;
        int fistRow = 2;
        int lastRow = 4632;
        String filePath01 = "C:\\Users\\llh\\Desktop\\股票数据拉取安排\\股票数据拉取安排\\表格0.2_全部A股-年报市盈率-2018~2020.xlsx";
        File file = new File(filePath01);
        String[] clNames = new String[]{
                "s_code", "s_name", "pe18", "pe19", "pe20"
        };
        Map<String, Map<String, Object>> mapMap = null;
        try{
            mapMap = PoiUtil.readSheet02(file, sheerNum, fistRow, lastRow, clNames);
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("读取表格数据报错");
            return;
        }

        List<Pe> peList = new LinkedList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for (int i = fistRow - 1; i < lastRow; i++) {
            try {
                Map<String, Object> map = mapMap.get(String.valueOf(i));
                Pe pe = new Pe();
                pe.setS_code((String) map.get(clNames[0]));
                pe.setS_name((String) map.get(clNames[1]));
                pe.setPe18(map.get(clNames[2])!=null?Double.valueOf((String) map.get(clNames[2])):null);
                pe.setPe19(map.get(clNames[3])!=null?Double.valueOf((String) map.get(clNames[3])):null);
                pe.setPe20(map.get(clNames[4])!=null?Double.valueOf((String) map.get(clNames[4])):null);
                peList.add(pe);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        //去重(重写了equel和hashcode方法)
        peList = peList.stream().distinct().collect(Collectors.toList());
        System.out.println("第一张表读取完毕");
        //读第二张表
        int sheerNum02 = 0;
        int fistRow02 = 2;
        int lastRow02 = 4632;
        String filePath02 = "C:\\Users\\llh\\Desktop\\股票数据拉取安排\\股票数据拉取安排\\表格0.3_全部A股-总市值数据20190331-20210930.xlsx";
        File file02 = new File(filePath02);
        String[] clNames02 = new String[]{
                "s_code", "s_name", "mv20210930", "mv20210630", "mv20210331",
                "mv20201231", "mv20200930", "mv20200630", "mv20200331", "mv20191231",
                "mv20190930", "mv20190630", "mv20190331"

        };
        Map<String, Map<String, Object>> mapMap02 = null;
        try{
            mapMap02 = PoiUtil.readSheet02(file02, sheerNum02, fistRow02, lastRow02, clNames02);
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("读取表格数据报错");
            return;
        }
        List<Pe> peList02 = new LinkedList<>();
        for (int i = fistRow - 1; i < lastRow; i++) {
            try {
                Map<String, Object> map = mapMap02.get(String.valueOf(i));
                Pe pe = new Pe();
                pe.setS_code((String) map.get(clNames02[0]));
                pe.setS_name((String) map.get(clNames02[1]));
                pe.setMv20210930(map.get(clNames02[2])!=null?Double.valueOf((String) map.get(clNames02[2])):null);
                pe.setMv20210630(map.get(clNames02[3])!=null?Double.valueOf((String) map.get(clNames02[3])):null);
                pe.setMv20210331(map.get(clNames02[4])!=null?Double.valueOf((String) map.get(clNames02[4])):null);
                pe.setMv20201231(map.get(clNames02[5])!=null?Double.valueOf((String) map.get(clNames02[5])):null);
                pe.setMv20200930(map.get(clNames02[6])!=null?Double.valueOf((String) map.get(clNames02[6])):null);
                pe.setMv20200630(map.get(clNames02[7])!=null?Double.valueOf((String) map.get(clNames02[7])):null);
                pe.setMv20200331(map.get(clNames02[8])!=null?Double.valueOf((String) map.get(clNames02[8])):null);
                pe.setMv20191231(map.get(clNames02[9])!=null?Double.valueOf((String) map.get(clNames02[9])):null);
                pe.setMv20190930(map.get(clNames02[10])!=null?Double.valueOf((String) map.get(clNames02[10])):null);
                pe.setMv20190630(map.get(clNames02[11])!=null?Double.valueOf((String) map.get(clNames02[11])):null);
                pe.setMv20190331(map.get(clNames02[12])!=null?Double.valueOf((String) map.get(clNames02[12])):null);
                peList02.add(pe);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        //去重
        peList02 = peList02.stream().distinct().collect(Collectors.toList());
        System.out.println("第一张表读取完毕");
        //比较个集合,取并集
        for (Pe pe : peList) {
            for (Pe pe1 : peList02) {
                if(pe1.equals(pe)){
                    //股票编号相等copy变量值
                    pe.setMv20210930(pe1.getMv20210930());
                    pe.setMv20210630(pe1.getMv20210630());
                    pe.setMv20210331(pe1.getMv20210331());
                    pe.setMv20201231(pe1.getMv20201231());
                    pe.setMv20200930(pe1.getMv20200930());
                    pe.setMv20200630(pe1.getMv20200630());
                    pe.setMv20200331(pe1.getMv20200331());
                    pe.setMv20191231(pe1.getMv20191231());
                    pe.setMv20190930(pe1.getMv20190930());
                    pe.setMv20190630(pe1.getMv20190630());
                    pe.setMv20190331(pe1.getMv20190331());
                    break;
                }
            }
        }
        //第二次循环
        List<Pe> newList = new ArrayList<>();
        for (Pe pe : peList02) {
            boolean flag = false;
            for (Pe pe1 : peList) {
                if(pe1.equals(pe1)){
                    flag = true;
                    break;
                }
            }
            if(!flag){
                newList.add(pe);
            }
        }
        //将差集加入
        peList.addAll(newList);
        System.out.println("合并完毕");
        //读取完毕,插入数据库
        SqlSession sqlSesion = SqlSessionUtil.getSqlSesion();
        PeMapper mapper = sqlSesion.getMapper(PeMapper.class);
        mapper.insertBatch(peList);
        System.out.println("插入数据完毕");

    }

    public static void main(String[] args){
        System.out.println("开始读表");
        ReadPe();
        System.out.println("读表结束");
    }
}
